package com.apes.fn.scm.retailOrder.repository;

import com.apes.fn.scm.retailOrder.model.RetailOrderItem;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.voucher.model.VoucherItem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author HuJianlin
 * @create 2018-10-31 16:07
 */
public interface RetailOrderItemRepository extends CustomRepository<RetailOrderItem, String> {



    @Query("SELECT T FROM RetailOrderItem T WHERE T.order.id = :id")
    List<RetailOrderItem> findByRetailOrderId(@Param("id") String id);
    /**
     * fetch data by company id and start date and end Date
     *
     * @param startDate start date
     * @param endDate   end Date
     * @param companyId company id
     * @param pageable  pageable
     * @return Page<List < Map>>
     */
    @Query(value = "SELECT SERVERTYPEID, SERVERTYPENAME, PRICETOTAL, DISCOUNTAMOUNT, SALODERID,\n" +
            "       DEPTNAME, PRODUCTNAME, DATE_APPROVE, COMPANY_ID \n" +
            "  FROM (SELECT SERVERTYPEID, SERVERTYPENAME, PRICETOTAL, DISCOUNTAMOUNT,\n" +
            "                SALODERID, DEPTNAME, PRODUCTNAME, DATE_APPROVE, COMPANY_ID\n" +
            "           FROM (SELECT ORDER_TYPE SERVERTYPEID,DECODE(G.ORDER_TYPE, 'server', '服务销售', 'yearcard', '预收款--年卡',\n" +
            "                        'storedValue', '预收款--储值', 'generalOrder', '普通单',\n" +
            "                        'retailReturn', '退款订单','agencyPay','定损代收', G.ORDER_TYPE, '普通单') SERVERTYPENAME,\n" +
            "                         E.PRICE_TOTAL AS PRICETOTAL,\n" +
            "                         NVL(E.DISCOUNT_AMOUNT, 0) DISCOUNTAMOUNT,\n" +
            "                         E.ID SALODERID,\n" +
            "                         (SELECT NAME FROM MAS_DEPT WHERE ID = G.DEPT_ID) AS DEPTNAME,\n" +
            "                         (SELECT '[' || ID || ']' || NAME\n" +
            "                             FROM MAS_PRODUCT\n" +
            "                            WHERE ID = E.PRODUCT_ID) PRODUCTNAME,\n" +
            "                         GET_DATE(G.DATE_APPROVE) DATE_APPROVE, G.COMPANY_ID\n" +
            "                    FROM RETAIL_ORDER G, RETAIL_ORDER_ITEM E\n" +
            "                   WHERE G.ID = E.SALE_ID\n" +
            "                     AND EXISTS (SELECT 1\n" +
            "                            FROM RETAIL_ORDER_PAYMENT_METHOD B\n" +
            "                           WHERE B.RETAIL_ORDER_ID = G.ID)\n" +
            "                     AND G.STATE = 'done')\n" +
            "         UNION ALL\n" +
            "         SELECT 'adpayment' SERVERTYPEID, '预收款--订金' SERVERTYPENAME,\n" +
            "                B.PRICE_TOTAL AS PRICETOTAL, 0 DISCOUNTAMOUNT, A.ID,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) AS DEPTNAME,\n" +
            "                (SELECT '[' || ID || ']' || NAME\n" +
            "                    FROM MAS_PRODUCT\n" +
            "                   WHERE ID = B.PRODUCT_ID) PRODUCTNAME,\n" +
            "                GET_DATE(A.CREATE_DATE) DATE_APPROVE, A.COMPANY_ID\n" +
            "           FROM ADVANCE_PAYMENT A, ADVANCE_PAYMENT_ITEM B\n" +
            "          WHERE A.ID = B.ADVANCE_PAYMENT_ID\n" +
            "            AND EXISTS (SELECT 1\n" +
            "                   FROM ADVANCE_PAYMENT_METHOD G\n" +
            "                  WHERE G.PAYMENT_ID = A.ID)\n" +
            "            AND A.State = 'approve')\n" +
            " WHERE 1 = 1\n" +
            "   AND COMPANY_ID = ?1 \n" +
            "   AND SERVERTYPENAME = ?2 \n" +
            "   AND PRICETOTAL - DISCOUNTAMOUNT <> 0 \n" +
            "   AND DATE_APPROVE >= ?3 \n" +
            "   AND DATE_APPROVE <= ?4 \n",
            countQuery = "SELECT COUNT(*) \n" +
                    "  FROM (SELECT SERVERTYPEID, SERVERTYPENAME, PRICETOTAL, DISCOUNTAMOUNT,\n" +
                    "                SALODERID, DEPTNAME, PRODUCTNAME, DATE_APPROVE, COMPANY_ID\n" +
                    "           FROM (SELECT DECODE(G.ORDER_TYPE, NULL, 'generalOrder', G.ORDER_TYPE,\n" +
                    "                                 G.ORDER_TYPE) SERVERTYPEID,\n" +
                    "                         DECODE(G.ORDER_TYPE, 'server', '服务销售', 'yearcard', '预收款--年卡',\n" +
                    "                        'storedValue', '预收款--储值', 'generalOrder', '普通单',\n" +
                    "                        'retailReturn', '退款订单','agencyPay','定损代收', G.ORDER_TYPE, '普通单') SERVERTYPENAME,\n" +
                    "                         E.PRICE_TOTAL AS PRICETOTAL,\n" +
                    "                         NVL(E.DISCOUNT_AMOUNT, 0) DISCOUNTAMOUNT,\n" +
                    "                         E.ID SALODERID,\n" +
                    "                         (SELECT NAME FROM MAS_DEPT WHERE ID = G.DEPT_ID) AS DEPTNAME,\n" +
                    "                         (SELECT '[' || ID || ']' || NAME\n" +
                    "                             FROM MAS_PRODUCT\n" +
                    "                            WHERE ID = E.PRODUCT_ID) PRODUCTNAME,\n" +
                    "                         GET_DATE(G.DATE_APPROVE) DATE_APPROVE, G.COMPANY_ID\n" +
                    "                    FROM RETAIL_ORDER G, RETAIL_ORDER_ITEM E\n" +
                    "                   WHERE G.ID = E.SALE_ID\n" +
                    "                     AND EXISTS (SELECT 1\n" +
                    "                            FROM RETAIL_ORDER_PAYMENT_METHOD B\n" +
                    "                           WHERE B.RETAIL_ORDER_ID = G.ID)\n" +
                    "                     AND G.STATE = 'done')\n" +
                    "         UNION ALL\n" +
                    "         SELECT 'adpayment' SERVERTYPEID, '预收款--订金' SERVERTYPENAME,\n" +
                    "                B.PRICE_TOTAL AS PRICETOTAL, 0 DISCOUNTAMOUNT, A.ID,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) AS DEPTNAME,\n" +
                    "                (SELECT '[' || ID || ']' || NAME\n" +
                    "                    FROM MAS_PRODUCT\n" +
                    "                   WHERE ID = B.PRODUCT_ID) PRODUCTNAME,\n" +
                    "                GET_DATE(A.CREATE_DATE) DATE_APPROVE, A.COMPANY_ID\n" +
                    "           FROM ADVANCE_PAYMENT A, ADVANCE_PAYMENT_ITEM B\n" +
                    "          WHERE A.ID = B.ADVANCE_PAYMENT_ID\n" +
                    "            AND EXISTS (SELECT 1\n" +
                    "                   FROM ADVANCE_PAYMENT_METHOD G\n" +
                    "                  WHERE G.PAYMENT_ID = A.ID)\n" +
                    "            AND A.State = 'approve')\n" +
                    " WHERE 1 = 1\n" +
                    "   AND COMPANY_ID = ?1 \n" +
                    "   AND SERVERTYPENAME = ?2 \n" +
                    "   AND PRICETOTAL - DISCOUNTAMOUNT <> 0 \n" +
                    "   AND DATE_APPROVE >= ?3 \n" +
                    "   AND DATE_APPROVE <= ?4 \n"
                    ,
            nativeQuery = true
    )
    Page<Map> findByCompanyIdAndServeTypeAndCreateDate(String companyId,String servertypename, Date startDate, Date endDate, Pageable pageable);


    /**
     * fetch data by company id and start date and end Date
     *
     * @param startDate start date
     * @param endDate   end Date
     * @param companyId company id
     * @return List < Map>
     */
    @Query(value = "select t from VoucherItem t  " +
            "            where t.voucher.company.id = ?1 and t.voucher.originBusinessType='RetailOrder'" +
            "            and t.voucher.createDate>=?2 and t.voucher.createDate<=?3 and t.voucher.voucherType='CUSTOMER_SALE'")
    List<VoucherItem> findByCompanyIdAndCreateDate(String companyId, Date startDate, Date endDate);


    /**
     * fetch data by company id and start date and end Date
     *
     * @param source source
     * @param ordernumber   ordernumber
     * @return List < Map>
     */
    @Query(value = "SELECT E " +
            "  FROM RetailReturn C, RetailReturnItem D, RetailOrderItem E \n" +
            " WHERE E.id = D.retailOrderItem.id \n" +
            "   AND C.id = D.order.id \n" +
            "   AND C.orderType.id = 'RetailReturn.orderType.A' \n" +
            "   AND E.mainSalesman.id IS NOT NULL \n" +
            "   AND C.id = ?1 \n" +
            "   AND D.id = ?2 ")
    RetailOrderItem findByOriginAndOrdernumber(String source, String ordernumber);

    RetailOrderItem findByOriginnumber(String originnumber);

}
